Stored Procedure Configuration Reference

The following topics provide detailed information regarding various configuration options associated with creating data services based on stored procedures. 

In Mode, Out Mode, Inout Mode

In, Out, and Inout mode settings determine how a parameter passed to a stored procedure is handled.

Procedure Profile

Each element in a stored procedure is associated with a type. If the item is a simple type, you can simply choose from the pop-up list of types. If the type is complex, you may need to supply an appropriate schema. Click on the schema location button and either enter a schema pathname or browse to a schema. The schema must reside in your application.

After selecting a schema, both the path to the schema file and the URI appear. 

Complex Parameter Types

Complex parameter types are supported under only three conditions:

• As the output parameter
• As the Return type
• As a rowset

About Rowsets

A rowset type is a complex type.

The rowset type contains a sequence of a repeatable elements (for example called CUSTOMER) with the fields of the rowset.

In some cases the wizard can automatically detect the structure of a rowset and create an element structure. However, if the structure is unknown, you will need to provide it. 

All rowset-type definitions must conform to this structure.

The name of the rowset type can be:

• The parameter name (in case of a input/output or output only parameter).
• An assigned name.
• The referenced element name (result rowsets) in a user-specified schema.

Not all databases support rowsets. In addition, JDBC does not report information related to defined rowsets.

Using Rowset Information 

In order to create data services from stored procedures that use rowset information, you need to supply the correct ordinal (matching number) and a schema. If the schema has multiple global elements, select the one you want from the Type column. Otherwise the type used match the first global element in your schema file.

The order of rowset information is significant; it must match the order in your data source. Use the Move Up / Move Down commands to adjust the ordinal number assigned to the rowset.

XML types in data services generated from stored procedures do not display native types. However, you can view the native type in the Source editor; it is located in the pragma section.

Stored Procedure Version Support

Only the most recent version of a particular stored procedure can be imported into ALDSP. For this reason you cannot identify a stored procedure version number when creating a physical data service based on a stored procedure. Similarly, adding a version number for your stored procedure in the Source editor will result in a query exception.

Supporting Stored Procedures with Nullable Input Parameter(s)

If you know that an input parameter of a stored procedure is nullable (can accept null values), you can change the signature of the function in Source View to make such parameters optional by adding a question mark at end of the parameter.

For example (question-mark (?) shown in bold):

function myProc($arg1 as xs:string) ...

would become:

function myProc($arg1 as xs:string?) ...